<?php
/**
 * Created by <xinxin.deng>.
 * Author: xinxin.deng
 * Date: 2018/8/22 9:27
 */
ini_set('max_execution_time','0');
error_reporting(E_ALL || ~E_NOTICE);
$str_cms_dir = dirname(dirname(__FILE__)) . DIRECTORY_SEPARATOR;
include_once $str_cms_dir . 'nn_logic' . DIRECTORY_SEPARATOR . 'nl_common.func.php';
$dc = nl_get_log_dc(array (
    "db_policy" 	=> NL_DB_WRITE,
    "cache_policy" 	=> NP_KV_CACHE_TYPE_MEMCACHE
));
//查询库中所有不相符的表格字段
if(isset($_POST['sdb']) &&  isset($_POST['db']))
{
    $standard_db = $_POST['sdb'];
    $db = $_POST['db'];

    $check_table = 'nns_update_check_table';
    $check_field = 'nns_update_check_field';
    $check_index = 'nns_update_check_index';

    //统一导入sql中的库名字段与本地库名一致
    $sql = "select `nns_db_name` from `{$check_table}` WHERE `nns_st_db_name` = '{$standard_db}'";
    $result = nl_query_by_db($sql, $dc->db());
    if (is_array($result))
    {
        $sql = "update `{$check_table}` set `nns_db_name` = '{$db}' where `nns_st_db_name` = '{$standard_db}'";
        nl_execute_by_db($sql , $dc->db());
    }
    //查询索引信息
    if (isset($_POST['index']))
    {
        $sql = "SELECT t.`nns_db_name`,t.`nns_table_name`,i.`nns_index_type`,i.`nns_index_name`,i.`nns_column`,i.`nns_version` FROM {$check_table} AS t JOIN `{$check_index}` AS i ON t.`nns_id`=i.`nns_table_id` WHERE t.`nns_st_db_name` = '{$standard_db}' ORDER BY i.`nns_version`";
        $rows = nl_query_by_db($sql, $dc->db());
        if (is_array($rows))
        {
            $arr_index = array();
            foreach ($rows as $key => &$row)
            {
                $arr_index[$key]['db_name'] = $row['nns_db_name'];
                $arr_index[$key]['table_name'] = $row['nns_table_name'];
                if ($row['nns_index_type'] == 'primary')
                {
                    $arr_index[$key]['index_name'] = $row['nns_index_type'];
                }
                else
                {
                    $arr_index[$key]['index_name'] = $row['nns_index_name'];
                }
                $arr_index[$key]['should_nns_column'] = $row['nns_column'];//应为字段
                $arr_index[$key]['version'] = $row['nns_version'];
                $arr_compare = compare_index($arr_index[$key]['db_name'], $arr_index[$key]['table_name'], $arr_index[$key]['index_name'], $arr_index[$key]['should_nns_column'], $standard_db);
                $arr_index[$key]['now_nns_column'] = $arr_compare['now_nns_column'];
                $arr_index[$key]['status'] = $arr_compare['status'];
            }
        }
        echo json_encode($arr_index);
    }
    //查询数据库字段信息
    else
    {
        //联合查询出update_check_table和update_check_field的数据
        $sql = "select t.`nns_db_name` `db_name`,t.`nns_table_name` `table_name`,f.`nns_name` `field_name`,f.`nns_type` `field_type`,f.nns_version version from `{$check_table}` as `t` JOIN `{$check_field}` as `f` ON `t`.`nns_id` = `f`.`nns_table_id` WHERE t.`nns_db_name` = '{$db}' ORDER BY f.`nns_version`";

        $rows = nl_query_by_db($sql, $dc->db());
        if (is_array($rows))
        {
            //遍历出需要检查的数据信息
            foreach ($rows as &$row) {
                $db_name = $row['db_name'];
                $table_name = $row['table_name'];
                $field_name = $row['field_name'];
                $row['field_type'] = strtolower($row['field_type']);
                $field_type = $row['field_type'];
                $version = $row['version'];
                //比较真实数据和需要检查的数据,返回现有字段类型和状态
                $arr = compare_db($db_name, $table_name, $field_name, $field_type, $standard_db);
                $row['now_field_type'] = $arr['now_field_type'];
                $row['status'] = $arr['status'];
            }
        }
        echo json_encode($rows);
    }
}
//根据搜索条件查询表中字段是否正确
if(isset($_GET) && !empty($_GET))
{
    $db_name = $_GET['db_name'];
    $stdb = $_GET['stdb'];

    $check_table = 'nns_update_check_table';
    $check_field = 'nns_update_check_field';
    $check_index = 'nns_update_check_index';

    //统一导入sql中的库名字段与本地库名一致
    if ($stdb != $db_name)
    {
        $sql = "select `nns_db_name` from `{$check_table}` WHERE `nns_st_db_name` = '{$stdb}'";
        $result = nl_query_by_db($sql, $dc->db());
        if (is_array($result))
        {
            $sql = "update `{$check_table}` set `nns_db_name` = '{$db_name}' where `nns_st_db_name` = '{$stdb}'";
            nl_execute_by_db($sql , $dc->db());
        }
    }
    $table_name = strtolower(trim($_GET['table_name']));
    $res = array();
    if (isset($_GET['index']))
    {
        if (!strlen($db_name) > 0 || !strlen($table_name) > 0)
        {
            $res['res'] = 0;
            $res['ts'] = '数据库和表名不能为空';
        }
        else
        {
            $sql = "SELECT t.`nns_db_name`,t.`nns_table_name`,i.`nns_index_type`,i.`nns_index_name`,i.`nns_column`,i.`nns_version` FROM `{$check_index}` AS i JOIN (SELECT `nns_id`,`nns_db_name`,`nns_table_name` FROM `{$check_table}` WHERE `nns_db_name` = '{$db_name}' AND `nns_table_name` = '{$table_name}') as t on t.nns_id = i.nns_table_id";
            $rows = nl_query_by_db($sql, $dc->db());
            if(!is_array($rows))
            {
                $res['ret'] = 2;
                $res['ts'] = '没有匹配到查询条件的数据';
            }
            else
            {
                $res['ret'] =1;
                $arr_index = array();
                foreach ($rows as $key => &$row)
                {
                    $arr_index[$key]['db_name'] = $row['nns_db_name'];
                    $arr_index[$key]['table_name'] = $row['nns_table_name'];
                    if ($row['nns_index_type'] == 'primary')
                    {
                        $arr_index[$key]['index_name'] = $row['nns_index_type'];
                    }
                    else
                    {
                        $arr_index[$key]['index_name'] = $row['nns_index_name'];
                    }
                    $arr_index[$key]['should_nns_column'] = $row['nns_column'];//应为字段
                    $arr_index[$key]['version'] = $row['nns_version'];
                    $arr_compare = compare_index($arr_index[$key]['db_name'], $arr_index[$key]['table_name'], $arr_index[$key]['index_name'], $arr_index[$key]['should_nns_column'], $stdb);
                    $arr_index[$key]['now_nns_column'] = $arr_compare['now_nns_column'];
                    $arr_index[$key]['status'] = $arr_compare['status'];
                }
                $res['row'] = $arr_index;
            }
        }
    }
    else
    {
        $field_name = strtolower(trim($_GET['field_name']));
        if(!strlen($db_name)>0 || !strlen($table_name)>0)
        {
            $res['ret'] = 0;
            $res['ts'] = '数据库和表名不能为空';
        }
        else
        {
            if(strlen($field_name)>0)
            {
                $sql = "SELECT nns_type field_type,nns_version version FROM `{$check_field}` WHERE `nns_name` = '{$field_name}' AND `nns_table_id` = (SELECT `nns_id` FROM `{$check_table}` WHERE `nns_db_name` = '{$db_name}' AND `nns_table_name` = '{$table_name}')";
                $row = nl_db_get_one($sql, $dc->db());
                if ($row === null)
                {
                    $res['ret'] = 2;
                    $res['ts'] = '没有匹配到查询条件的数据';
                }
                else
                {
                    $res['ret'] =1;
                    $row['db_name'] = $db_name;
                    $row['table_name'] = $table_name;
                    $row['field_name'] = $field_name;
                    $arr = compare_db($db_name, $table_name, $field_name, $row['field_type'], $stdb);
                    $row['now_field_type'] = $arr['now_field_type'];
                    $row['status'] = $arr['status'];
                    $res['row'][0] = $row;
                }
            }
            else
            {
                $sql = "SELECT t.nns_db_name db_name,t.nns_table_name table_name,f.nns_name field_name,nns_type field_type,nns_version version FROM `{$check_field}` as f JOIN (SELECT `nns_id`,`nns_db_name`,`nns_table_name` FROM `{$check_table}` WHERE `nns_db_name` = '{$db_name}' AND `nns_table_name` = '{$table_name}') as t on t.nns_id = f.nns_table_id";
                $rows = nl_query_by_db($sql, $dc->db());
                if(!is_array($rows)){
                    $res['ret'] = 2;
                    $res['ts'] = '没有匹配到查询条件的数据';
                }else{
                    $res['ret'] =1;
                    foreach ($rows as &$row)
                    {
                        $db_name = $row['db_name'];
                        $table_name = $row['table_name'];
                        $field_name = $row['field_name'];
                        $row['field_type'] = strtolower($row['field_type']);
                        $field_type = $row['field_type'];
                        $version = $row['version'];
                        //比较真实数据和需要检查的数据,返回现有字段类型和状态
                        $arr = compare_db($db_name, $table_name, $field_name, $field_type, $stdb);
                        $row['now_field_type'] = $arr['now_field_type'];
                        $row['status'] = $arr['status'];
                    }
                    $res['row'] = $rows;
                }

            }
        }
    }
    echo json_encode($res);
}
if(isset($_POST['export']))
{
    include_once "./check_index.php";
    $arr_match = explode(';',$str);
    $ret = array(
        'ret' => 0,
        'reason' =>'导入成功'
    );
    foreach($arr_match as $match)
    {
        $sql = trim($match);
        if(strlen($sql) >0)
        {
            $sql .= ';';
            $res = nl_execute_by_db($sql, $dc->db());
            if ($res === false)
            {
                $ret = array(
                    'ret' => 1,
                    'reason' => $sql
                );
                echo json_encode($ret);die;
            }
        }
    }
    echo json_encode($ret);
}

/**
 * @param string $db_name
 * @param string $table_name
 * @param string $field_name
 * @param string $field_type
 * @param string $stdb
 * @return array 返回检查状态(未创建此表|未添加该字段|字段类型不匹配)
 *               应为字段(长度)
 */
function compare_db($db_name, $table_name, $field_name, $field_type, $stdb)
{
    $stdb = substr($stdb,strrpos($stdb,'_')+1);
    switch ($stdb)
    {
        case 'dynamic':
            $dc = nl_get_dy_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
        case 'log':
            $dc = nl_get_log_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
        case 'aaa':
            $dc = nl_get_aaa_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
        case 'pay':
            $dc = nl_get_pay_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
        default:
            $dc = nl_get_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
    }
    //检查对应的库和表是否存在
    $sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='{$db_name}' and TABLE_NAME='{$table_name}'";
    $res_table = nl_db_get_one($sql, $dc->db());
    $arr =array();
    //未能查到相应库中的表
    if (!is_array($res_table))
    {
        $arr['now_field_type'] = '';
        $arr['status'] = '未创建此表';
        return $arr;
    }
    //根据查到的表名获取字段名和字段类型
    $sql = "SELECT  COLUMN_NAME as `f_name`,COLUMN_TYPE as `f_type` FROM information_schema.`COLUMNS` where TABLE_NAME = '{$res_table['TABLE_NAME']}' AND table_schema= '{$db_name}'";
    $res_field = nl_query_by_db($sql, $dc->db());
    $lists = array();
    if (is_array($res_field))
    {
        foreach ($res_field as $item)
        {
            $lists[$item['f_name']] = strtolower($item['f_type']);
        }
    }
    $list = array_keys($lists);
    //判断表中是否存在需要检查的字段
    if (!in_array($field_name, $list))
    {
        $arr['now_field_type'] = '';
        $arr['status'] = '未添加该字段';
        return $arr;
    }
    if ($lists["$field_name"] != $field_type)
    {
        $arr['now_field_type'] = $lists["$field_name"];
        $arr['status'] = '字段类型不匹配';
        return $arr;
    }
    else{
        $arr['now_field_type'] = $field_type;
        $arr['status'] = '正常';
        return $arr;
    }


}

/**
 * @param string $db_name
 * @param string $table_name
 * @param string $index_key
 * @param string $column_name
 * @param string $stdb
 * @return array 返回检查状态(未创建此表|未添加该索引|该索引不匹配)
 */
function compare_index($db_name, $table_name , $index_key, $column_name ,$stdb)
{
    $stdb = substr($stdb,strrpos($stdb,'_')+1);
    $index_key = strtolower($index_key);
    switch ($stdb)
    {
        case 'dynamic':
            $dc = nl_get_dy_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
        case 'log':
            $dc = nl_get_log_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
        case 'aaa':
            $dc = nl_get_aaa_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
        case 'pay':
            $dc = nl_get_pay_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
        default:
            $dc = nl_get_dc(array(
                "db_policy" => NL_DB_WRITE,
                "cache_policy" => NP_KV_CACHE_TYPE_MEMCACHE
            ));
            break;
    }
    //检查对应的库和表是否存在
    $sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='{$db_name}' and TABLE_NAME='{$table_name}'";
    $res_table = nl_db_get_one($sql, $dc->db());
    $arr = array();
    //未能查到相应库中的表
    if (!is_array($res_table))
    {
        $arr['now_nns_column'] = '';
        $arr['status'] = '未创建此表';
        return $arr;
    }
    $sql = "show index from `{$db_name}`.`{$table_name}`";
    $res_index = nl_query_by_db($sql, $dc->db());
    $key_result = array();
    $arr_key = array();
    foreach ($res_index as $key => &$field_key)
    {
        if (in_array($field_key['Key_name'], $arr_key))
        {
            $k = array_search($field_key['Key_name'], $arr_key);
            if (!is_null($field_key['Sub_part']))
            {
                $key_result[$k]['column_name'] .= ',' . $field_key['Column_name'].'('.$field_key['Sub_part'].')';

            }
            else
            {
                $key_result[$k]['column_name'] .= ',' . $field_key['Column_name'];
            }
            continue;
        }
        $key_result[$key]['key_name'] = strtolower($field_key['Key_name']);
        if (!is_null($field_key['Sub_part']))
        {
            $key_result[$key]['column_name'] = $field_key['Column_name'].'('.$field_key['Sub_part'].')';
        }
        else
        {
            $key_result[$key]['column_name'] = $field_key['Column_name'];
        }
        $arr_key[$key] = $field_key['Key_name'];
    }
    unset($res_index);
    unset($arr_key);
    $key_result = np_array_rekey($key_result, 'key_name');
    $list = array_keys($key_result);
    if (!in_array($index_key, $list))
    {
        $arr['now_nns_column'] = '';
        $arr['status'] = '未添加该索引';
        return $arr;
    }
    if ($key_result[$index_key]['column_name'] != $column_name)
    {
        $arr['now_nns_column'] = $key_result[$index_key]['column_name'];
        $arr['status'] = '该索引不匹配';
        return $arr;
    }
    else
    {
        $arr['now_nns_column'] = $column_name;
        $arr['status'] = '正常';
        return $arr;
    }
}